"""
Case Type   : delete
Case Name   : 多表删除，delete 表1 [*] from 表2，表3 where条件
Create At   : 2024/07/25
Owner       : songjing20
Description :
    1.兼容B库下建表
    2.delete 表1 from 表2,表3 where条件
    3.delete 表1 * from 表2,表3 where条件
    4.清理环境
Expect      :
    1.成功
    2.delete成功
    3.delete成功
    4.成功
History     :
"""

import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class TestDelete(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_delete_0023'
        self.tb1_name = 't_delete_0023_1'
        self.tb2_name = 't_delete_0023_2'
        self.tb3_name = 't_delete_0023_3'
        self.log.info('-----创建B模式数据库-----')
        create_db_res = self.pri_sh.execut_db_sql(
            f"drop database if exists {self.db_name};"
            f"create database {self.db_name} dbcompatibility 'B';")
        self.log.info(create_db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_db_res,
                      "建库失败")

    def test_delete(self):
        text = '-----step1:兼容B库下建表;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.tb1_name} cascade;
            drop table if exists {self.tb2_name} cascade;
            drop table if exists {self.tb3_name} cascade;
            create table {self.tb1_name}(col1 int,col2 varchar(10));
            create table {self.tb2_name}(col1 int,col2 varchar(10));
            create table {self.tb3_name}(col1 int,col2 varchar(10));
            insert into {self.tb1_name} values(1, '1'), (2, 'a');
            insert into {self.tb2_name} values(1, '1'), (2, 'b');
            insert into {self.tb3_name} values(1, '1'), (2, 'c');''',
            dbname=self.db_name)
        self.log.info(res)
        self.assertEqual(res.count(self.constant.CREATE_TABLE_SUCCESS),
                         3, f'创建表失败: {text}')
        self.assertEqual(res.count(self.constant.INSERT_SUCCESS_MSG),
                         3, f'创建表失败: {text}')

        text = '-----step2:delete 表1 from 表2,表3 where条件;expect:delete成功-----'
        self.log.info(text)
        sql = f'''begin;
        delete {self.tb1_name} a from {self.tb2_name} b, {self.tb3_name} c 
        where a.col2 = b.col2 and b.col2 = c.col2;
        select * from {self.tb1_name};
        rollback;'''
        sql_res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, sql_res, f'执行失败: {text}')
        self.assertIn('2 | a\n(1 row)', sql_res, f'执行失败: {text}')
        self.assertIn(self.constant.ROLLBACK_MSG, sql_res, f'执行失败: {text}')

        text = '-----step3:delete 表1 * from 表2，表3 where条件;expect:执行成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f"delete {self.tb1_name} * as a "
            f"from {self.tb2_name} b, {self.tb3_name} c "
            f"where a.col1 = b.col1 and b.col1 = c.col1;"
            f"select * from {self.tb1_name};", dbname=self.db_name)
        self.log.info(res)
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, res,
                      f'执行失败: {text}')
        self.assertIn('(0 rows)', res, f'执行失败: {text}')

    def tearDown(self):
        text = '-----step4:清理环境; expect:成功-----'
        self.log.info(text)
        drop_res = self.pri_sh.execut_db_sql(
            f"drop table if exists {self.tb1_name} cascade;"
            f"drop table if exists {self.tb2_name} cascade;"
            f"drop table if exists {self.tb3_name} cascade;"
            f"drop database if exists {self.db_name};")
        self.log.info(drop_res)
        self.assertEqual(drop_res.count(self.constant.DROP_TABLE_SUCCESS), 3,
                         f'删库失败: {text}')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res,
                      f'删库失败: {text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
